﻿namespace EFS.DB
{
	/// Connection class
	public class Connection : System.IDisposable
	{
		#region Composition

		// SQL connection 
		private System.Data.SqlClient.SqlConnection sqlConnection = null;

		// SQL transaction
		private System.Data.SqlClient.SqlTransaction sqlTransaction = null;

		#endregion

		#region Constructor

		/// <summary>
		/// Constructor
		/// </summary>
		/// <param name="connectionString"></param>
		public Connection(System.String connectionString)
		{
			// check
			EFS.Common.Verify.String(connectionString, "connectionString");

			// set 
			ConnectionString = connectionString;
			
			// make new SQL connection
			sqlConnection = new System.Data.SqlClient.SqlConnection(ConnectionString);

			// state changed 
			sqlConnection.StateChange += new System.Data.StateChangeEventHandler(sqlConnection_StateChange);

			// open connection
			sqlConnection.Open();
		}

		#endregion

		#region Usage

		/// Property ConnectionString: Get, Set
		public System.String ConnectionString { get; private set; }

		/// <summary>
		/// Begin transaction
		/// </summary>
		public void BeginTransaction()
		{
			if (null != sqlTransaction)
			{
				throw new System.InvalidOperationException("Already in transaction");
			}

			// commit
			sqlTransaction = sqlConnection.BeginTransaction();
		}

		/// <summary>
		/// Commit
		/// </summary>
		public void CommitTransaction()
		{
			if (null == sqlTransaction)
			{
				throw new System.InvalidOperationException("Not in transaction");
			}

			// commit
			sqlTransaction.Commit();
			sqlTransaction = null;
		}

		/// <summary>
		/// Rollback
		/// </summary>
		public void RollbackTransaction()
		{
			if (null == sqlTransaction)
			{
				throw new System.InvalidOperationException("Not in transaction");
			}

			// commit
			sqlTransaction.Rollback();
			sqlTransaction = null;
		}

		/// <summary>
		/// T loader Delegate
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="row"></param>
		/// <returns></returns>
		public delegate T RowLoader<T>(System.Data.DataRow row);

		/// <summary>
		/// Table reader
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="procedureName"></param>
		/// <param name="parametersArray"></param>
		/// <param name="rowLoader"></param>
		/// <param name="commandType"></param>
		/// <returns></returns>
		public T[] DoExecuteTableReaderQuery<T>(System.String procedureName, EFS.DB.Parameters.Array parametersArray, RowLoader<T> rowLoader, System.Data.CommandType commandType = System.Data.CommandType.StoredProcedure)
		{
			// create list
			System.Collections.Generic.List<T> list = new System.Collections.Generic.List<T>();

			// get data
			using (System.Data.DataSet resultDataSet = DoExecuteQuery(procedureName, parametersArray, commandType))
			{
				// load data
				if (!resultDataSet.HasErrors && resultDataSet.Tables.Count > 0)
				{
					// get tables
					using (System.Data.DataTable table = resultDataSet.Tables[0])
					{
						foreach (System.Data.DataRow row in table.Rows)
						{
							list.Add(rowLoader(row));
						}
					}
				}
			}

			// get array
			return list.ToArray();
		}

		/// <summary>
		/// Row reader
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="procedureName"></param>
		/// <param name="parametersArray"></param>
		/// <param name="rowLoader"></param>
		/// <param name="commandType"></param>
		/// <returns></returns>
		public T DoExecuteRowReaderQuery<T>(System.String procedureName, EFS.DB.Parameters.Array parametersArray, RowLoader<T> rowLoader, System.Data.CommandType commandType = System.Data.CommandType.StoredProcedure)
		{
			// get data
			using (System.Data.DataSet resultDataSet = DoExecuteQuery(procedureName, parametersArray, commandType))
			{
				// load data
				if (!resultDataSet.HasErrors && resultDataSet.Tables.Count > 0)
				{
					// get tables
					using (System.Data.DataTable table = resultDataSet.Tables[0])
					{
						foreach (System.Data.DataRow row in table.Rows)
						{
							return rowLoader(row);
						}
					}
				}
			}

			// get default
			return default(T);
		}

		/// <summary>
		/// Execute non query
		/// </summary>
		/// <param name="procedureName"></param>
		/// <param name="parametersArray"></param>
		/// <param name="commandType"></param>
		public void DoExecuteNonQuery(System.String procedureName, EFS.DB.Parameters.Array parametersArray, System.Data.CommandType commandType = System.Data.CommandType.StoredProcedure)
		{
			// init procedure
			using (System.Data.SqlClient.SqlCommand sqlCommand = Init(procedureName, parametersArray, commandType))
			{
				// execute
				sqlCommand.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Execute non query
		/// </summary>
		/// <param name="procedureName"></param>
		/// <param name="sqlParameters"></param>
		public void DoExecuteNonQuery(System.String inlineQuery)
		{
			// init procedure
			using (System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(inlineQuery, sqlConnection))
			{
				// set transaction
				sqlCommand.Transaction = sqlTransaction;

				// set procedure
				sqlCommand.CommandType = System.Data.CommandType.Text;

				// execute
				sqlCommand.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Execute query
		/// </summary>
		/// <param name="procedureName"></param>
		/// <param name="parametersArray"></param>
		/// <param name="commandType"></param>
		/// <returns></returns>
		public System.Data.DataSet DoExecuteQuery(
			System.String procedureName, 
			EFS.DB.Parameters.Array parametersArray,
			System.Data.CommandType commandType = System.Data.CommandType.StoredProcedure)
		{
			// dataset to return
			System.Data.DataSet dataSet = new System.Data.DataSet();

			// init procedure
			using (System.Data.SqlClient.SqlCommand sqlCommand = Init(procedureName, parametersArray, commandType))
			{
				// data adapter
				using (System.Data.SqlClient.SqlDataAdapter salDataAdapter = new System.Data.SqlClient.SqlDataAdapter())
				{
					// get data reader on exec
					salDataAdapter.SelectCommand = sqlCommand;

					// Fill
					salDataAdapter.Fill(dataSet);
				}
			}

			// ok
			return dataSet;
		}

		#endregion

		#region Destructor

		/// <summary>
		/// Dispose
		/// </summary>
		public void Dispose()
		{
			try
			{
				// close
				if (null != sqlTransaction)
				{
					// rollback
					sqlTransaction.Rollback();
					sqlTransaction.Dispose();
					sqlTransaction = null;
				}

				// close
				if (null != sqlConnection)
				{
					// close
					if (sqlConnection.State != System.Data.ConnectionState.Closed)
					{
						sqlConnection.Close();
					}

					// clean
					sqlConnection.Dispose();
					sqlConnection = null;
				}
			}
			catch (System.Exception ex)
			{
				EFS.EventLog.Log.LogException(ex, null);
			}
		}

		/// <summary>
		/// Destructor
		/// </summary>
		~Connection()
		{
			// close
			if (null != sqlTransaction)
			{
				EFS.EventLog.Log.LogError("EFS.DB.Connection Finalized but Transaction not Closed yet", null);
			}

			// close
			if (null != sqlConnection)
			{
				EFS.EventLog.Log.LogError("EFS.DB.Connection Finalized but Conection not Closed yet", null);
			}
		}

		#endregion

		#region Runtime

		/// <summary>
		/// Init SQL
		/// </summary>
		/// <param name="procedureName"></param>
		/// <param name="parametersArray"></param>
		/// <param name="commandType"></param>
		/// <returns></returns>
		private System.Data.SqlClient.SqlCommand Init(System.String procedureName,
			EFS.DB.Parameters.Array parametersArray,
			System.Data.CommandType commandType = System.Data.CommandType.StoredProcedure)
		{
			// check connection state and try to open
			if (sqlConnection.State == System.Data.ConnectionState.Closed ||
				sqlConnection.State == System.Data.ConnectionState.Broken)
			{
				// try to open
				sqlConnection.Open();
			}

			// make command
			System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(procedureName, sqlConnection);
			sqlCommand.Transaction = sqlTransaction;

			// set procedure
			sqlCommand.CommandType = commandType;

			// cch add 20100625 : null check if call is without params
			if (parametersArray != null)
			{
				// add parameters
				foreach (System.Data.SqlClient.SqlParameter sqlParamter in parametersArray.SQLParametersArray)
				{
					sqlCommand.Parameters.Add(sqlParamter);
				}
			}

			// done
			return sqlCommand;
		}

		/// <summary>
		/// Connection changes
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void sqlConnection_StateChange(object sender, System.Data.StateChangeEventArgs e)
		{
			try
			{
				// try to re-open when broken
				if (e.CurrentState == System.Data.ConnectionState.Broken)
				{
					// close
					sqlConnection.Close();

					// re-open
					sqlConnection.Open();
				}
			}
			catch (System.Exception ex)
			{
				//log
				EFS.EventLog.Log.LogException(ex);
			}
		}

		#endregion
	}
}